1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3
4 Public Class frmOverallReport
5
6 Dim a, b, c, d, f, g, h, i As Decimal
7
8 Sub Reset()
9 dtpDateFrom.Text = Today
10 dtpDateTo.Text = Today
11 End Sub
12 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
13 Reset()
14 End Sub
15
16
17 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
18 Me.Close()
19 End Sub
20
21 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
22 Cursor = Cursors.Default
23 Timer1.Enabled = False
24 End Sub
25
26
27 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
28 Try
29 Cursor = Cursors.WaitCursor
30 Timer1.Enabled = True
31 Dim rpt As New rptOverall 'The report you created.
32 Dim myConnection As SqlConnection
33 Dim MyCommand, MyCommand1, MyCommand2, MyCommand3 As New SqlCommand()
34 Dim myDA, myDA1, myDA2, myDA3 As New SqlDataAdapter()
35 Dim myDS As New DataSet 'The DataSet you created.
36 myConnection = New SqlConnection(cs)
37 MyCommand.Connection = myConnection
38 MyCommand1.Connection = myConnection
39 MyCommand2.Connection = myConnection
40 MyCommand3.Connection = myConnection
41 MyCommand.CommandText = "SELECT Customer.ID, Customer.Name, Customer.Gender, Customer.Address, Customer.City, Customer.State, Customer.ZipCode, Customer.ContactNo, Customer.EmailID, Customer.Remarks,Customer.Photo, InvoiceInfo.Inv_ID, InvoiceInfo.InvoiceNo, InvoiceInfo.InvoiceDate, InvoiceInfo.CustomerID , InvoiceInfo.GrandTotal, InvoiceInfo.TotalPaid, InvoiceInfo.Balance, Invoice_Product.IPo_ID, Invoice_Product.InvoiceID, Invoice_Product.ProductID, Invoice_Product.CostPrice, Invoice_Product.SellingPrice, Invoice_Product.Margin,Invoice_Product.Qty, Invoice_Product.Amount, Invoice_Product.DiscountPer, Invoice_Product.Discount, Invoice_Product.VATPer, Invoice_Product.VAT, Invoice_Product.TotalAmount, Product.PID,Product.ProductCode, Product.ProductName FROM Customer INNER JOIN InvoiceInfo ON Customer.ID = InvoiceInfo.CustomerID INNER JOIN Invoice_Product ON InvoiceInfo.Inv_ID = Invoice_Product.InvoiceID INNER JOIN Product ON Invoice_Product.ProductID = Product.PID where InvoiceDate between @d1 and @d2 order by InvoiceDate"
42 MyCommand.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
43 MyCommand.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
44 MyCommand1.CommandText = "SELECT * FROM Service INNER JOIN Customer ON Service.CustomerID = Customer.ID INNER JOIN InvoiceInfo1 ON Service.S_ID = InvoiceInfo1.ServiceID INNER JOIN Invoice1_Product ON InvoiceInfo1.Inv_ID = Invoice1_Product.InvoiceID where InvoiceInfo1.InvoiceDate between @d1 and @d2 order by invoiceDate"
45 MyCommand1.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
46 MyCommand1.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
47 MyCommand2.CommandText = "SELECT Distinct Stock.ST_ID, Stock.Stock_ID, Stock.Date, Stock.SupplierID, Stock.GrandTotal, Stock.TotalPayment, Stock.PaymentDue, Stock.Remarks, Stock_Product.SP_ID, Stock_Product.StockID, Stock_Product.ProductID,Stock_Product.Qty, Stock_Product.Price, Stock_Product.TotalAmount, Supplier.ID, Supplier.SupplierID AS Expr1, Supplier.Name, Supplier.Address, Supplier.City, Supplier.State, Supplier.ZipCode,Supplier.ContactNo, Supplier.EmailID, Supplier.Remarks AS Expr2, Product.PID, Product.ProductCode, Product.ProductName, Product.SubCategoryID, Product.Description, Product.CostPrice, Product.SellingPrice,Product.Discount, Product.VAT, Product.ReorderPoint FROM Stock INNER JOIN Stock_Product ON Stock.ST_ID = Stock_Product.StockID INNER JOIN Supplier ON Stock.SupplierID = Supplier.ID INNER JOIN Product ON Stock_Product.ProductID = Product.PID where Stock.Date between @d1 and @d2 order by Stock.Date"
48 MyCommand2.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
49 MyCommand2.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
50 MyCommand3.CommandText = "SELECT Voucher.Id, Voucher.VoucherNo, Voucher.Name, Voucher.Date, Voucher.Details, Voucher.GrandTotal, Voucher_OtherDetails.VD_ID, Voucher_OtherDetails.VoucherID, Voucher_OtherDetails.Particulars,Voucher_OtherDetails.Amount, Voucher_OtherDetails.Note FROM Voucher INNER JOIN Voucher_OtherDetails ON Voucher.Id = Voucher_OtherDetails.VoucherID where Voucher.Date between @d1 and @d2 order by Voucher.Date"
51 MyCommand3.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
52 MyCommand3.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
53 MyCommand.CommandType = CommandType.Text
54 MyCommand1.CommandType = CommandType.Text
55 MyCommand2.CommandType = CommandType.Text
56 MyCommand3.CommandType = CommandType.Text
57 myDA.SelectCommand = MyCommand
58 myDA1.SelectCommand = MyCommand1
59 myDA2.SelectCommand = MyCommand2
60 myDA3.SelectCommand = MyCommand3
61 myDA.Fill(myDS, "InvoiceInfo")
62 myDA.Fill(myDS, "Invoice_Product")
63 myDA.Fill(myDS, "Customer")
64 myDA.Fill(myDS, "Product")
65 myDA1.Fill(myDS, "InvoiceInfo1")
66 myDA1.Fill(myDS, "Invoice1_Product")
67 myDA1.Fill(myDS, "Service")
68 myDA1.Fill(myDS, "Customer")
69 myDA2.Fill(myDS, "Stock")
70 myDA2.Fill(myDS, "Stock_Product")
71 myDA2.Fill(myDS, "Product")
72 myDA2.Fill(myDS, "Supplier")
73 myDA3.Fill(myDS, "Voucher")
74 myDA3.Fill(myDS, "Voucher_OtherDetails")
75 con = New SqlConnection(cs)
76 con.Open()
77 Dim ct As String = "select ISNULL(sum(GrandTotal),0),ISNULL(sum(TotalPaid),0),ISNULL(sum(Balance),0) from InvoiceInfo where InvoiceDate between @d1 and @d2"
78 cmd = New SqlCommand(ct)
79 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
80 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
81 cmd.Connection = con
82 rdr = cmd.ExecuteReader
83 If (rdr.Read()) Then
84 a = rdr.GetValue(0)
85 b = rdr.GetValue(1)
86 c = rdr.GetValue(2)
87
88 Else
89 a = 0
90 b = 0
91 c = 0
92 End If
93 con.Close()
94 con = New SqlConnection(cs)
95 con.Open()
96 Dim ct1 As String = "select ISNULL(sum(Margin),0) from InvoiceInfo,Invoice_Product where InvoiceInfo.Inv_ID=Invoice_Product.InvoiceID and InvoiceDate between @d1 and @d2"
97 cmd = New SqlCommand(ct1)
98 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
99 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
100 cmd.Connection = con
101 rdr = cmd.ExecuteReader
102 If (rdr.Read()) Then
103 d = rdr.GetValue(0)
104 Else
105 d = 0
106 End If
107 con.Close()
108 con = New SqlConnection(cs)
109 con.Open()
110 Dim ct2 As String = "select ISNULL(sum(GrandTotal),0),ISNULL(sum(TotalPayment),0),ISNULL(sum(PaymentDue),0) from Stock,Supplier where Supplier.ID=Stock.SupplierID and Date between @d3 and @d4"
111 cmd = New SqlCommand(ct2)
112 cmd.Parameters.Add("@d3", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
113 cmd.Parameters.Add("@d4", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
114 cmd.Connection = con
115 rdr = cmd.ExecuteReader()
116 While rdr.Read()
117 f = rdr.GetValue(0)
118 g = rdr.GetValue(1)
119 h = rdr.GetValue(2)
120 End While
121 con.Close()
122 con = New SqlConnection(cs)
123 con.Open()
124 Dim ct3 As String = "select ISNULL(sum(GrandTotal),0) from Voucher where Date between @d1 and @d2"
125 cmd = New SqlCommand(ct3)
126 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
127 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
128 cmd.Connection = con
129 rdr = cmd.ExecuteReader()
130 While rdr.Read()
131 i = rdr.GetValue(0)
132 End While
133 rpt.Subreports(0).SetDataSource(myDS)
134 rpt.Subreports(1).SetDataSource(myDS)
135 rpt.Subreports(2).SetDataSource(myDS)
136 rpt.Subreports(3).SetDataSource(myDS)
137 rpt.SetParameterValue("p1", dtpDateFrom.Value.Date)
138 rpt.SetParameterValue("p2", dtpDateTo.Value.Date)
139 rpt.SetParameterValue("p3", a)
140 rpt.SetParameterValue("p4", b)
141 rpt.SetParameterValue("p5", c)
142 rpt.SetParameterValue("p6", d)
143 rpt.SetParameterValue("p7", Today)
144 rpt.SetParameterValue("p8", f)
145 rpt.SetParameterValue("p9", g)
146 rpt.SetParameterValue("p10", h)
147 rpt.SetParameterValue("p11", i)
148 frmReport.CrystalReportViewer1.ReportSource = rpt
149 frmReport.ShowDialog()
150 Catch ex As Exception
151 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
152 End Try
153 End Sub
154
155 Private Sub frmSalesReport_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
156
157 End Sub
158 End Class
